MySQL常用语句集

您所在的位置：网站首页 › create database character set › MySQL常用语句集

MySQL常用语句集

#MySQL常用语句集| 来源: 网络整理| 查看: 265

1 建表，插入数据

#选定数据库

use dt1; select * from user_info_utf;

#看当前使用哪个数据库

select database();

# 查看数据库里所有表名

show databases ; show tables ;

#修改字符集

alter database dt1 character set utf8; show create database dt1;

#创建数据库删除数据库

create database dt2x; drop database dt2x;

#创建表

use dt1 ; create table category( cid int , cname varchar(20) );

#建一张和test1相同结构的表

create table test1( tid int , tdate date ); create table test2 like test1 ;

#查看数据表的结构

desc test2 ;

#查看创建category这张表的SQL语句

show create table category;

#删除表

drop table test1 ;

#判断表是否存在，如果存在就删掉

drop table if exists test2;

#修改表名

rename table category to categpry2 ; rename table categpry2 to category ; desc category;

#向表里添加字段

alter table category add name2 varchar(20); desc category;

# 修改已有字段的数据类型或者长度

alter table category modify name2 int ; desc category; alter table category modify name2 varchar(50) ; desc category;

#修改列名称

alter table category change name2 name1 varchar(20); desc category ;

# 删除列

alter table category drop name1 ; desc category ;

# 在表里插数据

insert into category (cid,cname) values (1111,'lundi') ; select * from category ; insert into category (cid) values (0000) ; select * from category ; insert into category values(2222,'mardi'); select * from category ;

#修改表里的值

#带条件改

update category set cid = 20 where cid = 1111; select * from category ;

# 一次修改多列

update category set cid = 11,cname = 'vendredi' where cid = 0 ; select * from category ;

# 删掉所有数据,表本身还存在，也可以加where筛选删除特定数据

delete from category ; select * from category ;

#装填一条数据

insert into category values(111,'jeudi') ;

#一个优化删除速度的操作是先删掉整个表，再建一个一摸一样的表。

truncate table category ; select * from category ;2 查取数据

#准备数据

create table emp( eid int , ename varchar(20), sex char(1), salary double , hire_date date , dept_name varchar(20) );

#添加数据

INSERT INTO emp VALUES(1,'孙悟空 ','男 ',7200,'2013-02-04','教学部 '); INSERT INTO emp VALUES(2,'猪八戒 ','男 ',3600,'2010-12-02','教学部 '); INSERT INTO emp VALUES(3,'唐僧 ','男 ',9000,'2008-08-08','教学部 '); INSERT INTO emp VALUES(4,'白骨精 ','女 ',5000,'2015-10-07','市场部 '); INSERT INTO emp VALUES(5,'蜘蛛精 ','女 ',5000,'2011-03-14','市场部 '); INSERT INTO emp VALUES(6,'玉兔精 ','女 ',200,'2000-03-14','市场部 '); INSERT INTO emp VALUES(7,'林黛玉 ','女 ',10000,'2019-10-07','财务部 '); INSERT INTO emp VALUES(8,'黄蓉 ','女 ',3500,'2011-09-14','财务部 '); INSERT INTO emp VALUES(9,'吴承恩 ','男 ',20000,'2000-03-14',NULL); INSERT INTO emp VALUES(10,'孙悟饭 ','男 ', 10,'2020-03-14','财务部 ');

# 查询所有数据

select * from emp ;

#查单个字段

select eid, ename from emp ;

#给列起别名

select eid as '编号', ename as '姓名', sex as '性别', salary as '薪资', hire_date '入职时间', dept_name '部门名称' from emp ;

# 去重

select distinct dept_name from emp ;

# 字段数字计算

select salary + 1000 from emp ;

# 条件查询

select * from emp where dept_name is null ; select * from emp where dept_name is not null ;

#in 可以匹配多个值

select * from emp where salary in (7200,3600);

#or 也可以实现匹配多值

select * from emp where salary = 7200 or salary = 3600;

#第二个字为'悟'的员工信息

# 因为字间有空格，所以用两个下划线占位

select * from emp where ename like '__悟%';

#排序按薪水从大到小默认是升序

select * from emp order by salary desc; select * from emp order by salary;

# 还可以组合排，如果薪资一样，就让入职晚的在上

select * from emp order by salary desc , hire_date desc ;

#聚合函数count ,sum ,max,min,avg

select count(eid),sum(salary),max(hire_date),min(hire_date),avg(salary) from emp ;

# 1和*的效果一样

select count(1) from emp ;

#注意count不统计空值

select count(dept_name) from emp ;

#分组函数group by ,只要是select 里出现过的字段，group by后边够可以放

select sex,avg(salary) from emp group by sex;

#计算部门平均薪资，小于3500的不看，用having来做

#部门是空值的也不要

select dept_name , avg(salary) from emp where dept_name is not null group by dept_name having avg(salary) >=3500;

#注意这里where 和having 的区别在于操作顺序上的区别，

#这导致having后面可以跟聚合函数，而where后面跟不了

#limit 限定返回结果的行数

#limit 有两个参数，前一个是起始位置，后一个是限定行数

select * from emp order by salary desc limit 2,1 ;

#只给一个参数的时候默认是从0开始

select * from emp order by salary desc limit 1 ;

#主键约束保证唯一性

# 法1

create table emp2( eid int primary key , ename varchar(20), sex char(1) ); -- drop table emp2 ;

# 法2

create table emp2( eid int , ename varchar(20), sex char(1), primary key(eid) ); -- drop table emp2 ;

#法3

create table emp2( eid int , ename varchar(20), sex char(1) ); alter table emp2 add primary key(eid); desc emp2 ; insert into emp2 values(1,'宋江','男');

#会报错因为主键不能为空

insert into emp2 values(null,'宋江','男');

#会报错因为主键不能重复

insert into emp2 values(1,'土豆','男');

#删除主键

alter table emp2 drop primary key ; desc emp2 ; drop table emp2 ;

#主键自增表

create table emp2( eid int primary key auto_increment , ename varchar(20), sex char(1) ); desc emp2 ; INSERT INTO emp2(ename,sex) VALUES('张三 ','男 '); INSERT INTO emp2(ename,sex) VALUES('李四 ','男 '); INSERT INTO emp2 VALUES(NULL,'翠花 ','女 '); select * from emp2 ;

# 修改自增起始值

create table emp3( eid int primary key auto_increment , ename varchar(20), sex char(1) )auto_increment=100; INSERT INTO emp3(ename,sex) VALUES('张三 ','男 '); INSERT INTO emp3(ename,sex) VALUES('李四 ','男 '); select * from emp3 ;

#两种删除对自增的影响是不同的，因为删除的机制不一样。

#delete会继续增加数字，而truncate会从0开始。

# 非空约束

create table emp4( eid int primary key auto_increment, ename varchar(20) not null, sex char(1) );

# 唯一约束

create table emp5( eid int primary key auto_increment, ename varchar(20) unique, sex char(1) );

#一个表只能有一个主键，其他约束不做数量限制

# 指定默认值

create table emp6( eid int primary key auto_increment, ename varchar(20) unique, sex char(1) default '女' ); insert into emp6(eid,ename,sex) values (1,'mercredi',default); select * from emp6 ; insert into emp6(eid,ename) values (2,'mercredix'); select * from emp6 ;

#外键

# 先建一个新库

create database db3_2 character set utf8; use db3_2;

#分类表主表

create table category( cid varchar(32) primary key , cname varchar(50) );

#商品表从表

create table products( pid varchar(32) primary key , pname varchar(50), price int , flag varchar(2), category_id varchar(32), foreign key(category_id) references category(cid) ); show tables ;

#分类数据

INSERT INTO category(cid,cname) VALUES('c001','家电 '); INSERT INTO category(cid,cname) VALUES('c002','鞋服 '); INSERT INTO category(cid,cname) VALUES('c003','化妆品 '); INSERT INTO category(cid,cname) VALUES('c004','汽车 ');

#商品数据

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视机 ',5000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调 ',3000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱 ',4500,'1','c001'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋 ',800,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤 ',200,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T 恤 ',300,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣 ',2000,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水 ',800,'1','c003'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝 ',200,'1','c003'); select * from category ; select * from products;

# 多表查询

# 内连接

select * from products,category where category_id = cid ; select * from products p join category c on p.category_id = c.cid ;

# 外连接就是 left join , right join

#union 合并查询结果消除重复行

#union all 不消除重复行

# 子查询就是嵌套

select * from products where price = (select max(price) from products ) ;3 函数：

use dt1 ;

#系统信息函数看当前用的哪个数据库，版本，使用者账号

select database(); select version(); select user();

#数学函数

select abs(-10); #绝对值 select floor(5.9); #向下取整 select ceil(5.9); #向上取整 select round(2.3333,2);#保留两位小数 select rand();#在0-1之间随机生成随机生成一个数 select pi();#圆周率 select mod(7,3);#取余数

#字符串函数

select concat('i','love','data') ; #拼接，还可以拼字段 select left('data',2);#从左取2位 select right('data',2);#从右取2位 select mid('data',1,2);#中间从第1个开始取，取2个 select substring('i love data',3,4);#从第3个开始取，取4个 select trim(' da ta '); #去除首尾空格 select replace(' da ta ',' ','$');#把串里所有空格换成$ select reverse('data');#翻转字符串

#日期和时间函数

select curdate(); #返回当前日期 select curtime(); #返回当前时间 select now();#返回当前日期和时间 select month('2023-02-06');#返回月份 select year('2023-02-06');#返回年份

#条件判断

select if(15 then 10 when 5>7 then 5 else 0 end

#窗函数

sum(...) over(partition by ... order by ... rows between...and ...) sum也可以是其他聚合函数，还可以是排序函数rank,dense_rank,row_number,还可以是切片函数ntile(n) 也可以是偏移函数前移lag(x,1,2),后移lead(x,1,2)偏移后面的两个参数是从哪开始移，移动多少 rows between 参数： rows between 2 preceding and current row # 取当前行和前面两行 rows between unbounded preceding and current row # 包括本行和之前所有的行 rows between current row and unbounded following # 包括本行和之后所有的行 rows between 3 preceding and current row # 包括本行和前面三行 rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行

更多技能分享见这篇汇总文章：

Sour Radish：数据分析技能汇总

【本文地址】

公司简介

联系我们